package com.jandar.service;

import com.alibaba.fastjson.JSONObject;
import com.jandar.entity.*;
import com.jandar.util.DynamicQueryUtil;
import com.jandar.util.QueryDslUtil;
import com.querydsl.core.BooleanBuilder;
import com.querydsl.core.Tuple;
import com.querydsl.core.types.Path;
import com.querydsl.jpa.impl.JPAQueryFactory;
import lombok.Data;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletRequest;
import java.util.*;
import java.util.stream.Collectors;

import static com.jandar.constant.QTableConstant.*;

@Service
public class StatisticsPersonService {
    @Autowired
    JPAQueryFactory queryFactory;

    @Autowired
    DynamicQueryUtil dynamicQueryUtil;
    @Autowired
    BillService billService;
    @Autowired
    BankService bankService;


    public JSONObject listPerson() {
        List<ExcelPerson> excelPeople = queryFactory.selectFrom(qExcelPerson).fetch();
        return result(excelPeople, excelPeople.size());
    }


    public JSONObject pageAllPersonInfoByRel(PageParam param, HttpServletRequest request) {
        List<JSONObject> list = new ArrayList<>();
        Map<String, String> rel = param.getRel();
        Map<String, String> entity = param.getEntity();
        String relTypeName = rel.get("excelSourceTypeName");
        if (relTypeName == null) {
            relTypeName = rel.get("type");
        }
        String personId = rel.getOrDefault("person_id", null);
        List<String> sendNumberList = null;
        List<String> receiveNumberList = null;
        ExcelSourceType excelSourceType = queryFactory.selectFrom(qExcelSourceType).where(qExcelSourceType.name.eq(relTypeName)).fetchOne();
        ExcelSourceTypeField excelSourceTypeField = queryFactory.selectFrom(qExcelSourceTypeField).where(qExcelSourceTypeField.typeId.eq(excelSourceType.getId())).fetchOne();
        Map<String,String> columns = new TreeMap<>();
        List<ExcelSourceTypeMapping> excelSourceTypeMappings = queryFactory.selectFrom(qExcelSourceTypeMapping).where(qExcelSourceTypeMapping.typeId.eq(excelSourceType.getId())).fetch();
        excelSourceTypeMappings.forEach(mapping -> {
            String toKey = mapping.getToKey();
            String fromKey = mapping.getFromKey();
            columns.put(toKey,fromKey);
        });
        String numberListSQL = "select number from excel_person_info where person_id = (select person_id from excel_person_info where number = '#{number}')";

        if (personId != null) {
            sendNumberList = queryFactory.select(qExcelPersonInfo.number).from(qExcelPersonInfo).where(qExcelPersonInfo.personId.eq(Integer.valueOf(personId))).fetch();
        } else {
            personId = String.valueOf(queryFactory.select(qExcelPersonInfo.personId).from(qExcelPersonInfo).where(qExcelPersonInfo.number.eq(rel.get(excelSourceTypeField.getSendNumberField())).and(qExcelPersonInfo.type.eq(excelSourceTypeField.getNumberType()))).fetchFirst());
            sendNumberList = dynamicQueryUtil.query(numberListSQL.replace("#{number}", rel.get(excelSourceTypeField.getSendNumberField()).replace("'", "\\'"))).stream().map(r -> String.valueOf(r.get("number"))).collect(Collectors.toList());
        }
        receiveNumberList = dynamicQueryUtil.query(numberListSQL.replace("#{number}", rel.get(excelSourceTypeField.getReceiveNumberField()).replace("'", "\\'"))).stream().map(r -> String.valueOf(r.get("number"))).collect(Collectors.toList());

        Path[] fields = {qExcelSourceType.tableName, qExcelSourceTypeField.sendNumberField, qExcelSourceTypeField.receiveNumberField, qExcelSourceTypeField.numberType};
        List<Tuple> typeTupleList = queryFactory.select(fields).from(qExcelSourceType).leftJoin(qExcelSourceTypeField).on(qExcelSourceType.id.eq(qExcelSourceTypeField.typeId)).where(qExcelSourceType.parentId.isNull()).fetch();

        List<Map<String, Object>> typeList = QueryDslUtil.tupleListToMapList(typeTupleList, fields);
        for (Map<String, Object> type : typeList) {

            Map<String, Object> condition = new HashMap<>();
            String sendNumberField = String.valueOf(type.get("send_number_field"));
            String receiveNumberField = String.valueOf(type.get("receive_number_field"));

            if (StringUtils.hasLength(sendNumberField)) {
                condition.put(sendNumberField + "_in", sendNumberList);
            } else {
                condition.put("person_id_equal", personId);
            }
            if (StringUtils.hasLength(receiveNumberField)) {
                condition.put(receiveNumberField + "_in", receiveNumberList);

            }
            Long count = dynamicQueryUtil.queryCount((String) type.get("table_name"), condition);
            List<Map<String, Object>> mapList = dynamicQueryUtil.query((String) type.get("table_name"),columns,condition,null,(param.num - 1) * param.size, param.size);
            list.add(result(mapList, count));
        }
        return result(list, typeList.size());

    }

    public JSONObject pagePersonInfoByRel(PageParam param, HttpServletRequest request) {
        Map<String, String> rel = param.getRel();
        Map<String, String> entity = param.getEntity();
        String relTypeName = rel.get("excelSourceTypeName");
        if (relTypeName == null) {
            relTypeName = rel.get("type");
        }
        ExcelSourceType excelSourceType = queryFactory.selectFrom(qExcelSourceType).where(qExcelSourceType.name.eq(relTypeName)).fetchOne();
        ExcelSourceTypeField excelSourceTypeField = queryFactory.selectFrom(qExcelSourceTypeField).where(qExcelSourceTypeField.typeId.eq(excelSourceType.getId())).fetchOne();
        Map<String,String> columns = new TreeMap<>();
        List<ExcelSourceTypeMapping> excelSourceTypeMappings = queryFactory.selectFrom(qExcelSourceTypeMapping).where(qExcelSourceTypeMapping.typeId.eq(excelSourceType.getId())).fetch();
        excelSourceTypeMappings.forEach(mapping -> {
            String toKey = mapping.getToKey();
            String fromKey = mapping.getFromKey();
            columns.put(toKey,fromKey);
        });
        Map<String,Object> condition = new HashMap<>();
        String numberListSQL = "select number from excel_person_info where person_id = (select person_id from excel_person_info where number = '#{number}')";
        if (StringUtils.hasLength(excelSourceTypeField.getSendNumberField())) {
            String sendNumberField = excelSourceTypeField.getSendNumberField();
            List<String> sendNumberList = dynamicQueryUtil.query(numberListSQL.replace("#{number}", rel.get(sendNumberField).replace("'", "\\'"))).stream().map(r -> String.valueOf(r.get("number"))).collect(Collectors.toList());
            condition.put(sendNumberField+"_in",sendNumberList);
        }
        if (StringUtils.hasLength(excelSourceTypeField.getReceiveNumberField())) {
            String receiveNumberField = excelSourceTypeField.getReceiveNumberField();
            List<String> receiveNumberList = dynamicQueryUtil.query(numberListSQL.replace("#{number}", rel.get(receiveNumberField).replace("'", "\\'"))).stream().map(r -> String.valueOf(r.get("number"))).collect(Collectors.toList());
            condition.put(receiveNumberField+"_in",receiveNumberList);
        }
        if (StringUtils.hasLength(rel.get("person_id"))) {
            condition.put("person_id_equal",rel.get("person_id"));
        }
        Long count = dynamicQueryUtil.queryCount(excelSourceType.getTableName(),condition);
        List<Map<String, Object>> source = dynamicQueryUtil.query(excelSourceType.getTableName(),columns,condition,null,(param.num - 1) * param.size, param.size);

        List<ExcelSourceTypeMapping> excelSourceTypeMappingList = queryFactory.selectFrom(qExcelSourceTypeMapping).where(qExcelSourceTypeMapping.typeId.eq(excelSourceType.getId())).fetch();
        Map<String, String> mapping = new HashMap<>();
        excelSourceTypeMappingList.forEach(item -> {
            mapping.put(item.getToKey(), item.getFromKey());
        });

        List<Map<String, Object>> res = new ArrayList<>();
        source.forEach(item -> {
            Map<String, Object> data = new TreeMap<>();
            item.entrySet().stream().filter(entry -> mapping.containsKey(entry.getKey())).forEach(entry -> {
                data.put(mapping.get(entry.getKey()), entry.getValue());
            });
            res.add(data);

        });
        return result(res, count);

    }

//    public JSONObject pagePerson(PageParam param, HttpServletRequest request) {
//        long userId = 0;
//        String username = "";
//
//        BooleanBuilder booleanBuilder = new BooleanBuilder();
//        if (param.getPersonId() != null) {
//            booleanBuilder.and(qExcelPerson.id.eq(param.getPersonId()));
//        }
//        if (param.getEntity() != null) {
//            if (StringUtils.hasLength(param.getEntity().get("idCard"))) {
//                booleanBuilder.and(qExcelPerson.idCard.contains(param.getEntity().get("idCard")));
//            }
//            if (StringUtils.hasLength(param.getEntity().get("name"))) {
//               booleanBuilder.and(qExcelPerson.name.contains(param.getEntity().get("name")));
//            }
//        }
//
//         booleanBuilder.and(qExcelPerson.isRespondents.eq(true)).and(qExcelPersonInfo.number.isNotNull()).and(qExcelPersonInfo.type.contains("银行").or(qExcelPersonInfo.type.contains("手机")));
////        TableOnConditionStep<Record> selectJoinStep = EXCEL_SOURCE_RECORD.leftJoin(qExcelPersonInfo).on(EXCEL_SOURCE_RECORD.ID.eq(qExcelPersonInfo.RECORD_ID)).leftJoin(STATISTICS_PERSON).on(qExcelPersonInfo.PERSON_ID.eq(STATISTICS_PERSON.ID));
//
//        Integer count = queryFactory.select(qExcelPerson.id).from(qExcelPerson).leftJoin(qExcelPersonInfo).on(qExcelPerson.id.eq(qExcelPersonInfo.personId))
//                .rightJoin(qExcelSourceRecord).on(qExcelPersonInfo.recordId.eq(qExcelSourceRecord.id))
//                .where(booleanBuilder).groupBy(qExcelPerson.id).fetch().size();
//        List<JSONObject> persons = queryFactory.selectFrom(qExcelPerson).leftJoin(qExcelPersonInfo).on(qExcelPerson.id.eq(qExcelPersonInfo.personId))
//                .rightJoin(qExcelSourceRecord).on(qExcelPersonInfo.recordId.eq(qExcelSourceRecord.id))
//                .where(booleanBuilder).groupBy(qExcelPerson.id).orderBy(qExcelPerson.id.desc()).offset(param.size * (param.num - 1)).limit( param.size).fetch()
//                .stream().map(r->JSONObject.parseObject(JSONObject.toJSONString(r))).collect(Collectors.toList());
//
//        persons = persons.stream().filter(p -> {
//            boolean flag = false;
//            List<ExcelPersonInfo> personInfoList = queryFactory.selectFrom(qExcelPersonInfo).where(qExcelPersonInfo.personId.eq(p.getInteger("id"))).fetch();
//            p.put("personInfoList", personInfoList);
////            List<Long> billIdList = new ArrayList<>();
////            List<Long> bankIdList = new ArrayList<>();
////            personInfoList.forEach(r->{
////                if (r.getType().contains("银行")) {
////                    bankIdList.add(r.getExcelSourceId());
////                } else if (r.getType().contains("手机")) {
////                    billIdList.add(r.getExcelSourceId());
////                }
////            });
////            int dataCount = queryFactory.selectCount().from(BILL_EXCEL_SOURCE_RECORD).where(BILL_EXCEL_SOURCE_RECORD.USER_ID.eq(userId).and(BILL_EXCEL_SOURCE_RECORD.ID.in(billIdList))).fetchOne(Long.class) + queryFactory.selectCount().from(BANK_EXCEL_SOURCE_RECORD).where(BANK_EXCEL_SOURCE_RECORD.USER_ID.eq(userId).and(BANK_EXCEL_SOURCE_RECORD.ID.in(bankIdList))).fetchOne(Long.class);
////            if (dataCount>0) {
////                flag = true;
////            }
//            String type = "";
//            try {
//                type = personInfoList.get(0).getType();
//            } catch (Exception e) {
//                e.printStackTrace();
//            }
//            if (type.contains("银行")) {
//                BankService.PageParam pageParam = new BankService.PageParam();
//                pageParam.setPersonId(Integer.parseInt(String.valueOf(p.get("id"))));
//                pageParam.setType("资产变化趋势");
//                p.put("type", "bill");
//                p.put("default1", bankService.pageBySheet(pageParam));
//                pageParam.setType("资产获取渠道占比");
//                p.put("default2", bankService.pageBySheet(pageParam));
//            } else if (type.contains("手机")) {
//                BillService.PageParam pageParam = new BillService.PageParam();
//                pageParam.setNum(1);
//                pageParam.setSize(2);
//                pageParam.setPersonId(Integer.parseInt(String.valueOf(p.get("id"))));
//                pageParam.setType("对方姓名频率");
//                p.put("type", "billed");
//                p.put("default1", billService.pageBySheet(pageParam));
//                pageParam.setType("通话基站频率");
//                p.put("default2", billService.pageBySheet(pageParam));
//            }
//            return true;
//        }).collect(Collectors.toList());
//
//        return result(persons, count);
//    }

//    public JSONObject addPerson(StatisticsPerson person) {
//        StatisticsPersonRecord statisticsPersonRecord = queryFactory.newRecord(STATISTICS_PERSON);
//        statisticsPersonRecord.from(person);
//        statisticsPersonRecord.insert();
//        return result("新增成功");
//    }
//
//    public JSONObject editPerson(StatisticsPerson person) {
//        StatisticsPersonRecord statisticsPersonRecord = queryFactory.newRecord(STATISTICS_PERSON);
//        statisticsPersonRecord.from(person);
//        statisticsPersonRecord.update();
//        return result("修改成功");
//    }
//
//    public JSONObject deletePerson(Integer personId) {
//        queryFactory.deleteFrom(STATISTICS_PERSON).where(STATISTICS_PERSON.ID.eq(personId)).execute();
//        return result("删除成功");
//    }
//
//    public JSONObject pagePersonInfo(PageParam param) {
//        Long count = queryFactory.selectCount().from(qExcelPersonInfo).where(qExcelPersonInfo.PERSON_ID.eq(param.personId)).fetchOne(Long.class);
//        List<StatisticsPersonInfo> persons = queryFactory.selectFrom(qExcelPersonInfo).where(qExcelPersonInfo.PERSON_ID.eq(param.personId)).limit(param.size * (param.num - 1), param.size).fetch(StatisticsPersonInfo.class);
//        return result(persons, count);
//    }
//
//    public JSONObject addPersonInfo(StatisticsPersonInfo person) {
//        StatisticsPersonInfoRecord StatisticsPersonInfoRecord = queryFactory.newRecord(qExcelPersonInfo);
//        StatisticsPersonInfoRecord.from(person);
//        StatisticsPersonInfoRecord.insert();
//        return result("新增成功");
//    }
//
//    public JSONObject editPersonInfo(StatisticsPersonInfo person) {
//        StatisticsPersonInfoRecord StatisticsPersonInfoRecord = queryFactory.newRecord(qExcelPersonInfo);
//        StatisticsPersonInfoRecord.from(person);
//        StatisticsPersonInfoRecord.update();
//        return result("修改成功");
//    }
//
//    public JSONObject deletePersonInfo(Long personInfoId) {
//        queryFactory.deleteFrom(qExcelPersonInfo).where(qExcelPersonInfo.ID.eq(personInfoId)).execute();
//        return result("删除成功");
//    }

    @Data
    public static class PageParam {
        private Map<String, String> rel;
        private Map<String, String> entity;
        private Long size;
        private Long num;
        private Integer personId;
    }

    private JSONObject result(Object data, Long total) {
        JSONObject res = new JSONObject();
        res.put("data", data);
        res.put("total", total);
        res.put("status", true);
        return res;
    }
    private JSONObject result(Object data, Integer total) {
        JSONObject res = new JSONObject();
        res.put("data", data);
        res.put("total", total);
        res.put("status", true);
        return res;
    }

    private JSONObject result(String msg) {
        JSONObject res = new JSONObject();
        res.put("msg", msg);
        res.put("status", true);
        return res;
    }

}
